use master
go
drop database SMS
go
create database SMS
go
use SMS
go
create table Categories(
	categoryId int primary key Identity not null,
	categoryName nvarchar(100) not null
)
go
create table Products(
	productId int primary key Identity not null,
	categoryId int foreign key references Categories(categoryId),
	productName nvarchar(50) not null,
	barcode nvarchar(20) null,
	description ntext null,
	price decimal null
)
go
create table Orders(
	orderId int primary key identity not null,
	orderDate datetime not null,
	customerName nvarchar(100) null
)
go
create table OrderDetails(
	orderId int foreign key references Orders(orderId),
	productId int foreign key references Products(productId),
	productPrice decimal not null,
	amount int not null,
	
	primary key (orderId, productId)	
)
